package com.snts.synchronization.schema;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.io.IOUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.mchange.io.FileUtils;
import com.snts.synchronization.core.BaseTable;
import com.snts.synchronization.core.Column;
import com.snts.synchronization.core.Synchronization;
import com.snts.synchronization.core.SyncTable;
import com.snts.synchronization.core.Column.SyncDirection;
import com.snts.synchronization.schema.extensions.ExtensionsGenerator;
import com.sun.xml.internal.ws.api.addressing.WSEndpointReference.Metadata;

public class SchemaUpdatesGenerator {

	private static final Log log = LogFactory.getLog(SchemaUpdatesGenerator.class);

	public SchemaUpdatesGenerator() {
		super();
	}

	public List<SchemaUpdate> generateUpdates(DatabaseMetaData metadata, List<SyncTable> tables) throws SQLException {
		List<SchemaUpdate> updates = new ArrayList<SchemaUpdate>();
		boolean schemaExists = schemaExists(metadata, "synchronization");
		if (!schemaExists && ts > 0) {
			updates.add(new SchemaUpdate("CREATE SCHEMA synchronization;"));
		}
		updates.addAll(new ExtensionsGenerator().generateExtensions());

		for (SyncTable t : tables) {
			updates.addAll(generateUpdates(metadata, t));
		}
		return updates;
	}

	public List<SchemaUpdate> generateUpdates(DatabaseMetaData metadata, SyncTable table) throws SQLException {

		List<SchemaUpdate> updates = new ArrayList<SchemaUpdate>();

		if (!logTableExists(metadata, table) && ts > 0) {
			StringBuffer sql = new StringBuffer("CREATE TABLE ");
			sql.append("synchronization.\"");
			sql.append(table.getLogTableName());
			sql.append("\"(id uuid not null default uuid_generate_v1(),\n update_type char(1) not null check(update_type in ('I','U')),");
			sql.append("update_time timestamp with time zone not null default clock_timestamp(),");
			sql.append("row_id uuid not null,");
			sql.append("cols text not null, \nvalues text not null, \nsync_rest_id uuid, \ntransaction_id uuid not null, primary key(id));");
			updates.add(new SchemaUpdate(sql.toString()));
		}

		try {
			updates.add(new SchemaUpdate(TriggerGenerator.generate(table), "Logging trigger function for table " + table.getQualifiedName()));
		} catch (IOException e) {
			e.printStackTrace();
		}

		if (!triggerExists(metadata, table) && ts > 0) {
			StringBuffer sql = new StringBuffer("CREATE TRIGGER ");
			sql.append(table.getTriggerName());
			sql.append(" AFTER INSERT OR UPDATE ON \"");
			sql.append(table.getName());
			sql.append("\"\n FOR EACH ROW \n");
			sql.append("EXECUTE PROCEDURE ");
			sql.append(table.getLogFunctionName());
			sql.append("();");
			updates.add(new SchemaUpdate(sql.toString()));
		}

		return updates;

	}

	public void installUpdates(Connection conn, Synchronization synchronization) throws SQLException {
		for (SchemaUpdate update : generateUpdates(conn.getMetaData(), synchronization.getTables())) {
			try {
				log.info("Installing update [" + update.getName() + "]");
				update.apply(conn);
			} catch (SQLException e) {
				log.error("Error applying schema update SQL[" + update.getSql() + "]", e);
			}
		}
	}

	public static final long ts = 1356912000000L;

	private boolean schemaExists(DatabaseMetaData metadata, String schema) throws SQLException {
		ResultSet rst = metadata.getSchemas();
		try {
			while (rst.next()) {
				// System.out.println(rst.getString("TABLE_SCHEM"));
				if (schema.equals(rst.getString("TABLE_SCHEM"))) {
					return true;
				}
			}
			return false;
		} finally {
			rst.close();
		}
	}

	private boolean logTableExists(DatabaseMetaData metadata, SyncTable table) throws SQLException {
		ResultSet rst = metadata.getTables(null, "synchronization", table.getLogTableName(), null);
		try {
			return rst.next();
		} finally {
			rst.close();
		}
	}

	private boolean triggerExists(DatabaseMetaData metadata, SyncTable table) throws SQLException {
		// TODO: improve logic to do more verifications and tests
		boolean insertOk;
		boolean updateOk;
		Connection conn = metadata.getConnection();
		String sql = "select * from information_schema.triggers where event_object_schema=? and event_object_table=? "
				+ "and trigger_name=? and event_manipulation=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, table.getSchema());
		pstmt.setString(2, table.getName());
		pstmt.setString(3, table.getTriggerName());
		pstmt.setString(4, "INSERT");
		ResultSet rs = pstmt.executeQuery();
		try {
			insertOk = rs.next();
		} finally {
			rs.close();
		}
		pstmt.setString(1, table.getSchema());
		pstmt.setString(2, table.getName());
		pstmt.setString(3, table.getTriggerName());		
		pstmt.setString(4, "UPDATE");
		rs = pstmt.executeQuery();
		try {
			updateOk = false;
		} finally {
			rs.close();
		}
		return insertOk && updateOk;
	}

	private boolean restIdColumnExists(DatabaseMetaData metadata, BaseTable table) throws SQLException {
		ResultSet rst = metadata.getColumns(table.getCatalog(), table.getSchema(), table.getName(), Constants.REST_ID_COLUMN_NAME);
		try {
			return rst.next();
		} finally {
			rst.close();
		}
	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("org.postgresql.Driver");
		Connection conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/testdb", "postgres", "123");

		Synchronization synchronization = new Synchronization().load(conn.getMetaData());
		SchemaUpdatesGenerator schemaUpdatesGenerator = new SchemaUpdatesGenerator();
		schemaUpdatesGenerator.installUpdates(conn, synchronization);
		
	    List<SchemaUpdate> updates = schemaUpdatesGenerator.generateUpdates(conn.getMetaData(),
		synchronization.getTables());
	    
		for (SchemaUpdate update : updates) {
			System.out.println(update.getSql()); System.out.println(); 
		}
	}

}
